<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_DICTIONARY AS
  (SELECT T.* ,
    row_number() over(order by T_DICTIONARY_TYPE.ORDER_NUM, T_DICTIONARY_TYPE.code, t.ORDER_NUM, t.CODE) rn,
  T_DICTIONARY_TYPE.code AS typeCode,
  T_DICTIONARY_TYPE.name AS typeName
  FROM C1_DICTIONARY T
LEFT JOIN C1_DICTIONARY_TYPE T_DICTIONARY_TYPE
ON (Sysdate BETWEEN T_DICTIONARY_TYPE.begin_date AND T_DICTIONARY_TYPE.end_date
AND T_DICTIONARY_TYPE.ID = t.type_id)
  WHERE Sysdate BETWEEN t.begin_date AND t.end_date
<#if StringUtils.isNotBlank(typeCode)>
and (instr(T_DICTIONARY_TYPE.code, :typeCode) > 0 or instr(T_DICTIONARY_TYPE.name, :typeCode) > 0)
</#if>
<#if StringUtils.isNotBlank(typeId)>
and T.TYPE_ID = :typeId
</#if>
<#if StringUtils.isNotBlank(code)>
and (instr(t.code, :code) > 0 or instr(t.name, :code) > 0)
</#if>
  )
SELECT T.*,
  T.typeCode,
  T.typeName
FROM w_DICTIONARY T
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>